Remove historical Log Databases
On this page, you will find information about how to automate the removal of historical Log Databases that you no longer require.
Nodinite spawns new databases according to the following System Parameters:
You can remove empty old Log Databases. However, even if you have historical Log Databases without any rows in the tables, these still occupy disk space and create overhead, including CPU, disk I/O, backups and more.
A very high risk is involved in dropping the correct set of Log Databases. The Nodinite admin needs to perform this removal operation independently.
The SQL DBA with the proper authority and responsibility may use the sample script on this page to manually or automatically perform the steps required to clean out historical Log Databases as part of planned maintenance.
Find the oldest number of days to keep messages as defined by the system
The following System parameters are in use to specify for how long to keep events and data by default:
- DaysToKeepMessageEventsDefault
- DaysToKeepMessageContextDefault
- DaysToKeepMessageDataDefault
- DaysToKeepMonitorEvents
Delete Log Databases from SQL Server
Use the following scripts to create or execute the script to remove Log Databases older than the specified time (for example 180 days) from the Nodinite instance.
SQL Variable | Description | Example |
---|---|---|
@prefix | The prefix part of Nodinite Log Databases | NodiniteLog_Prod_ |
@configDB | The name of the Nodinite Configuration Database | NodiniteConfig_Prod |
days | The number of days to go back in time, a positive integer | 180 |
Create a SQL job with 2 steps:
Step 1: Remove backup history and delete Old Nodinite Log Databases
-- NOTE: You MUST ensure the DECLARE part is the same in Step 1 and Step 2
DECLARE @prefix NVARCHAR(255) = 'NodiniteLog_Prod_',
@configDB NVARCHAR(255) = 'NodiniteConfig_Prod',
@days INT = 180 -- NOTE: Must be a positive integer
DECLARE @sql NVARCHAR(MAX) = 'USE [master]' + CHAR(13)+CHAR(10)
;WITH nodinitelogdatabases ([Database]) as
(SELECT [Database] From LogTables LT Where LT.[Database] LIKE (@prefix + '%') AND LT.[EndDate] IS NOT NULL AND LT.[EndDate] < DATEADD(DD, @days * -1, GetDate()) AND LT.[IsRemoteServer] = 0 )
SELECT @sql +=
'EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = [' + l.[Database] + ']' + CHAR(13)+CHAR(10) +
'ALTER DATABASE [' + l.[Database] + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE' + CHAR(13)+CHAR(10) +
'DROP DATABASE [' + l.[Database] + ']' + CHAR(13)+CHAR(10)
FROM nodinitelogdatabases l
ORDER BY [Database]
PRINT '-- Number of Log Databases to remove: ' + CAST(@@Rowcount AS VARCHAR(18))
PRINT @sql
-- EXEC sp_executesql @sql
Step 2: Remove Log Database entries from the LogTables table
-- NOTE: You MUST ensure the DECLARE part is the same in Step 1 and Step 2
DECLARE @prefix NVARCHAR(255) = 'NodiniteLog_Prod_',
@configDB NVARCHAR(255) = 'NodiniteConfig_Prod',
@days INT = 180 -- NOTE: Must be a positive integer
DECLARE @sql NVARCHAR(MAX)
SELECT @sql =
'USE [' + @configDB + ']' + CHAR(13)+CHAR(10) +
'DELETE FROM LogTables Where [Database] LIKE (''' + @prefix + '%'') AND [EndDate] IS NOT NULL AND [EndDate] < DATEADD(DD, ' + CAST(@days AS VARCHAR(18)) + ' * -1, GetDate()) AND [IsRemoteServer] = 0' + CHAR(13)+CHAR(10)
PRINT @sql
-- EXEC sp_executesql @sql